﻿using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using DataTable = System.Data.DataTable;

namespace Workflow.Common.ExcelHelp
{
	public class ExcelHelper
	{
		/// <summary>
		/// EXCEL转DATATABLE
		/// </summary>
		/// <param name="fileName"></param>
		/// <param name="sheetName"></param>
		/// <returns></returns>
		public static DataTable ImportFromExcel(string fileName, string sheetName = null)
		{

			if (String.IsNullOrEmpty(fileName))
			{
				throw new ArgumentNullException("fileName");
			}

			if (!File.Exists(fileName))
			{
				throw new FileNotFoundException(fileName);
			}

            string connString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; "

												+ "Extended Properties='Excel 8.0;HDR=YES;'", fileName);

			DataSet excelds = new DataSet();

			using (var conn = new OleDbConnection(connString))
			{

				conn.Open();

				DataTable excelsheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,

					new[] { null, null, null, "Table" });

				if (excelsheets.Rows.Count > 0)
				{
					string sql = String.Format("SELECT * FROM [{0}]",

						String.IsNullOrEmpty(sheetName) ?

						excelsheets.Rows[0]["TABLE_NAME"].ToString() :

						sheetName + "$");

					OleDbDataAdapter excelda = new OleDbDataAdapter(sql, conn);

					excelda.Fill(excelds);
				}
			}
			return excelds.Tables.Count > 0 ? excelds.Tables[0] : null;
		}
	}
	public class ExcelEdit  
	{
		public string mFilename;
		public Excel.Application app;
		public Excel.Workbooks wbs;
		public Excel.Workbook wb;
		public Excel.Worksheets wss;
		public Excel.Worksheet ws;
		public ExcelEdit()
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
		}
		public void Create()//创建一个Excel对象
		{
			app = new Excel.Application();
			wbs = app.Workbooks;
			wb = wbs.Add(true);
		}
		public void Open(string FileName)//打开一个Excel文件
		{
			app = new Excel.Application();
			wbs = app.Workbooks;
			wb = wbs.Add(FileName);
			//wb = wbs.Open(FileName, 0, true, 5,"", "", true, Excel.XlPlatform.xlWindows, "t", false, false, 0, true,Type.Missing,Type.Missing);
			//wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
			mFilename = FileName;
		}
		public Excel.Worksheet GetSheet(string SheetName)
		//获取一个工作表
		{
			Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets[SheetName];
			return s;
		}
		public Excel.Worksheet AddSheet(string SheetName)
		//添加一个工作表
		{
			Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
			s.Name = SheetName;
			return s;
		}

		public void DelSheet(string SheetName)//删除一个工作表
		{
			((Excel.Worksheet)wb.Worksheets[SheetName]).Delete();
		}
		public Excel.Worksheet ReNameSheet(string OldSheetName, string NewSheetName)//重命名一个工作表一
		{
			Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets[OldSheetName];
			s.Name = NewSheetName;
			return s;
		}

		public Excel.Worksheet ReNameSheet(Excel.Worksheet Sheet, string NewSheetName)//重命名一个工作表二
		{

			Sheet.Name = NewSheetName;

			return Sheet;
		}

		public void SetCellValue(Excel.Worksheet ws, int x, int y, object value)
		//ws：要设值的工作表     X行Y列     value   值
		{
			ws.Cells[x, y] = value;
		}
		public void SetCellValue(string ws, int x, int y, object value)
		//ws：要设值的工作表的名称 X行Y列 value 值
		{

			GetSheet(ws).Cells[x, y] = value;
		}

		public void SetCellProperty(Excel.Worksheet ws, int Startx, int Starty, int Endx, int Endy, int size, string name, Excel.Constants color, Excel.Constants HorizontalAlignment)
		//设置一个单元格的属性   字体，   大小，颜色   ，对齐方式
		{
			name = "宋体";
			size = 12;
			color = Excel.Constants.xlAutomatic;
			HorizontalAlignment = Excel.Constants.xlRight;
			ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name = name;
			ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size = size;
			ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color = color;
			ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment = HorizontalAlignment;
			
		}
		public void CopyCellProperty(Excel.Worksheet ws, int Startx, int Starty, int Endx, int Endy, int toStartx, int toStarty, int toEndx, int toEndy)
		//设置一个单元格的属性   字体，   大小，颜色   ，对齐方式
		{
			Excel.Range range1 = ws.get_Range((object)ws.Cells[Startx, Starty], (object)ws.Cells[Endx, Endy]);
			Excel.Range range2 = ws.get_Range((object)ws.Cells[toStartx, toStarty], (object)ws.Cells[toEndx, toEndy]);
			range2.Merge(Missing.Value);
			range1.Copy(Missing.Value);
			ws.Paste(range2, Missing.Value);

		}
		public void SetCellProperty(string wsn, int Startx, int Starty, int Endx, int Endy, int size, string name, Excel.Constants color, Excel.Constants HorizontalAlignment)
		{
			//name = "宋体";
			//size = 12;
			//color = Excel.Constants.xlAutomatic;
			//HorizontalAlignment = Excel.Constants.xlRight;

			Excel.Worksheet ws = GetSheet(wsn);
			ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name = name;
			ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size = size;
			ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color = color;

			ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment = HorizontalAlignment;
		}

		public void InsertRow(Worksheet ws, int RowNumber)
		{
			Excel.Range excelRange = ws.get_Range((object)ws.Cells[RowNumber, 1], (object)ws.Cells[RowNumber, 1]);

			excelRange.EntireRow.Insert(XlInsertShiftDirection.xlShiftDown, Type.Missing);
		}

		public void DeleteRow(Worksheet ws, int RowNumber)
		{
			Excel.Range excelRange = ws.get_Range((object)ws.Cells[RowNumber, 1], (object)ws.Cells[RowNumber, 1]);

			excelRange.EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);
		}

		public void UniteCells(Excel.Worksheet ws, int x1, int y1, int x2, int y2)
		//合并单元格
		{
			ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Type.Missing);
		}

		public void UniteCells(string ws, int x1, int y1, int x2, int y2)
		//合并单元格
		{
			GetSheet(ws).get_Range(GetSheet(ws).Cells[x1, y1], GetSheet(ws).Cells[x2, y2]).Merge(Type.Missing);

		}


		public void InsertTable(System.Data.DataTable dt, string ws, int startX, int startY)
		//将内存中数据表格插入到Excel指定工作表的指定位置 为在使用模板时控制格式时使用一
		{

			for (int i = 0; i <= dt.Rows.Count - 1; i++)
			{
				for (int j = 0; j <= dt.Columns.Count - 1; j++)
				{
					GetSheet(ws).Cells[startX + i, j + startY] = dt.Rows[i][j].ToString();

				}

			}

		}
		public void InsertTable(System.Data.DataTable dt, Excel.Worksheet ws, int startX, int startY)
		//将内存中数据表格插入到Excel指定工作表的指定位置二
		{

			for (int i = 0; i <= dt.Rows.Count - 1; i++)
			{
				for (int j = 0; j <= dt.Columns.Count - 1; j++)
				{

					ws.Cells[startX + i, j + startY] = dt.Rows[i][j];

				}

			}

		}


		public void AddTable(System.Data.DataTable dt, string ws, int startX, int startY)
		//将内存中数据表格添加到Excel指定工作表的指定位置一
		{

			for (int i = 0; i <= dt.Rows.Count - 1; i++)
			{
				for (int j = 0; j <= dt.Columns.Count - 1; j++)
				{

					GetSheet(ws).Cells[i + startX, j + startY] = dt.Rows[i][j];

				}

			}

		}
		public void AddTable(System.Data.DataTable dt, Excel.Worksheet ws, int startX, int startY)
		//将内存中数据表格添加到Excel指定工作表的指定位置二
		{


			for (int i = 0; i <= dt.Rows.Count - 1; i++)
			{
				for (int j = 0; j <= dt.Columns.Count - 1; j++)
				{

					ws.Cells[i + startX, j + startY] = dt.Rows[i][j];

				}
			}

		}
		public bool Save()
		//保存文档
		{
			if (mFilename == "")
			{
				return false;
			}
			else
			{
				try
				{
					wb.Save();
					return true;
				}

				catch (Exception ex)
				{
					return false;
				}
			}
		}
		public bool SaveAs(object FileName)
		//文档另存为
		{
			try
			{
				wb.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                //http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlfileformat.aspx
				//wb.SaveAs(FileName,
				//    //39
				//    56
				//    , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
				return true;

			}

			catch (Exception ex)
			{
				return false;
			}
		}
		public void Close()
		//关闭一个Excel对象，销毁对象
		{
			//wb.Save();
			if (wb != null)
			{
				wb.Close(false, Type.Missing, Type.Missing);
				NAR(wb);
			}
			if (wbs != null)
			{
				wbs.Close();
				NAR(wbs);
			}
			if (app!=null)
			{
				app.Quit();
			}
			NAR(app);
			wb = null;
			wbs = null;
			app = null;

			GC.Collect();
		}

		/// <summary>
		/// 释放资源
		/// </summary>
		/// <param name="o"></param>
		private void NAR(object o)
		{
			try
			{
				if (o !=null)
				{
					System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
				}
			}
			catch { }
			finally
			{
				o = null;
			}
		}

		
	}


}
